﻿using ModelLayer;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;

namespace DataAccessObjectLayer
{
    /// <summary>
    /// Class DBReservation.
    /// </summary>
    public class DBReservation : DBClass, IDBReservation
    {
        private List<Customer> customers;
        private List<Route> routes;

        /// <summary>
        /// Gets all reservations from the database.
        /// </summary>
        /// <returns>List{Reservation}.</returns>
        public List<Reservation> GetAllReservation()
        {
            DBCustomer dbCustomer = new DBCustomer();
            customers = dbCustomer.GetAllCustomer();
            DBRoute dbRoute = new DBRoute();
            routes = dbRoute.GetAllRoutes();
            return MiscWhere("").Cast<Reservation>().ToList();
        }

        /// <summary>
        /// Gets all reservations from a customer.
        /// </summary>
        /// <param name="customerId">The customer identifier.</param>
        /// <returns>List{Reservation}.</returns>
        public List<Reservation> GetAllReservationFromCustomer(long customerId)
        {
            DBCustomer dbCustomer = new DBCustomer();
            customers = dbCustomer.GetAllCustomer();
            DBRoute dbRoute = new DBRoute();
            routes = dbRoute.GetAllRoutes();
            string wClause = " FK_PersonId = " + customerId;
            return MiscWhere(wClause).Cast<Reservation>().ToList();
        }

        /// <summary>
        /// Inserts a reservation in the database.
        /// </summary>
        /// <param name="resv">The resv.</param>
        /// <returns>The number of affected rows</returns>
        /// <exception cref="System.Exception">InsertPartReservation Error</exception>
        public int InsertReservation(Reservation resv)
        {
            int nextId = GetMax.GetMaxId("SELECT MAX(Id) FROM Reservation");
            nextId = nextId + 1;
            int rc = -1;
            const string query = "INSERT INTO Reservation(Id, Date,Status,FK_PersonId,FK_RouteId) " +
                                 "VALUES(@Id,@Date,@Status,@FK_PersonId,@FK_RouteId)";

            var dbCon = new DBCon();
            using (SqlConnection con = (SqlConnection)dbCon.GetConnection())
            {
                con.Open();
                SqlTransaction transaction = con.BeginTransaction(IsolationLevel.Serializable);
                var dbRoute = new DBRoute();
                try
                {
                    int routeId = dbRoute.InsertRoute(resv.Route, con, transaction);
                    if (routeId == -1)
                    {
                        throw new Exception("The route was not inserted");
                    }
                    using (var cmdIns = new SqlCommand(query, con, transaction))
                    {
                        cmdIns.Parameters.Add(new SqlParameter("@Id", SqlDbType.BigInt) { Value = nextId });
                        cmdIns.Parameters.Add(new SqlParameter("@Date", SqlDbType.DateTime) { Value = resv.Date });
                        cmdIns.Parameters.Add(new SqlParameter("@Status", SqlDbType.NVarChar) { Value = resv.Status });
                        cmdIns.Parameters.Add(new SqlParameter("@FK_PersonId", SqlDbType.BigInt) { Value = resv.Customer.Id });
                        cmdIns.Parameters.Add(new SqlParameter("@FK_RouteId", SqlDbType.BigInt) { Value = routeId });

                        rc = cmdIns.ExecuteNonQuery();

                        var dbPartResv = new DBPartReservation();
                        dbPartResv.InsertPartReservations(resv.PartReservations, nextId, con, transaction);
                    }
                    if (rc == -1)
                    {
                        throw new Exception("The reservation was not inserted");
                    }
                    transaction.Commit();
                }
                catch (Exception e)
                {
                    TryRollback(e, transaction, "Insert in DBReservation");
                }
                con.Close();
            }

            //return the reservationId instead of rc - this is used in another context
            return nextId;
        }

        /// <summary>
        /// Gets a reservation from the database.
        /// </summary>
        /// <param name="resvId">The resv identifier.</param>
        /// <returns>Reservation.</returns>
        public Reservation GetReservation(long resvId)
        {
            string wClause = "Id = " + resvId;
            return (Reservation)SingleWhere(wClause);
        }

        /// <summary>
        /// Updates a reservation in the database.
        /// </summary>
        /// <param name="resv">The resv.</param>
        /// <returns>the number of affected rows</returns>
        public int UpdateReservation(Reservation resv)
        {
            int rc = -1;
            const string query = "UPDATE Reservation SET "
                                 + "Date = @Date, "
                                 + "Status = @Status "
                                 + " WHERE Id = @Id";

            var dbCon = new DBCon();

            using (SqlConnection connection = (SqlConnection)dbCon.GetConnection())
            {
                SqlTransaction transaction;
                SqlCommand cmdUpd = SqlCommand(connection, out transaction);

                try
                {
                    cmdUpd.CommandText = query;

                    using (cmdUpd)
                    {
                        cmdUpd.Parameters.Add(new SqlParameter("@Id", SqlDbType.BigInt) { Value = resv.Id });
                        cmdUpd.Parameters.Add(new SqlParameter("@Status", SqlDbType.NVarChar) { Value = resv.Status });
                        cmdUpd.Parameters.Add(new SqlParameter("@Date", SqlDbType.DateTime) { Value = resv.Date });
                        rc = cmdUpd.ExecuteNonQuery();
                    }
                    if (rc == -1)
                    {
                        throw new Exception("The reservation was not updated");
                    }
                    transaction.Commit();
                }

                catch (Exception e)
                {
                    TryRollback(e, transaction, "Update in DBReservation");
                }

                return rc;
            }
        }

        /// <summary>
        /// Removes a reservation from the database.
        /// </summary>
        /// <param name="resvId">The resv id.</param>
        /// <returns>the number of affected rows</returns>
        public int RemoveReservation(long resvId)
        {
            int rc = -1;
            const string query = "DELETE FROM Reservation " + " WHERE Id = @Id";
            var dbCon = new DBCon();

            using (SqlConnection connection = (SqlConnection)dbCon.GetConnection())
            {
                SqlTransaction transaction;
                var cmdDel = SqlCommand(connection, out transaction);

                try
                {
                    cmdDel.CommandText = query;

                    using (cmdDel)
                    {
                        cmdDel.Parameters.Add(new SqlParameter("@Id", SqlDbType.BigInt) { Value = resvId });
                        rc = cmdDel.ExecuteNonQuery();
                    }
                    if (rc == -1)
                    {
                        throw new Exception("The reservation was not deleted");
                    }
                    transaction.Commit();
                }

                catch (Exception e)
                {
                    TryRollback(e, transaction, "Delete in DBReservation");
                }

                return rc;
            }
        }

        /// <summary>
        /// Builds a query with a 'where'-clause.
        /// </summary>
        /// <param name="wClause">The where clause.</param>
        /// <returns>an sql query string</returns>
        protected override string BuildQuery(string wClause)
        {
            var query = "SELECT * FROM Reservation";
            if (wClause.Length > 0)
            {
                query += " WHERE " + wClause;
            }
            return query;
        }

        /// <summary>
        /// Builds an object from either a dataset or a datareader. The bool 'connected' defines if
        /// it is one or the other.
        /// </summary>
        /// <param name="dataSetOrDataReader">A datarow or datareader.</param>
        /// <param name="connected">if set to <c>true</c> [connected](datareader).</param>
        /// <returns>Object to be cast to the correct type in the specific implementation</returns>
        protected override object BuildObject(object dataSetOrDataReader, bool connected)
        {
            Reservation resv = null;
            IDBCustomer dbPerson = new DBCustomer();
            IDBRoute dbRoute = new DBRoute();
            IDBPartReservation dbPartReservation = new DBPartReservation();

            if (connected) //This means that a datareader will be used
            {
                var dataReader = dataSetOrDataReader as SqlDataReader;

                if (dataReader != null)
                    resv = new Reservation
                    {
                        Id = (long)dataReader["Id"],
                        Customer = dbPerson.GetCustomer((long)dataReader["FK_PersonId"]),
                        Date = (DateTime)dataReader["Date"],
                        Route = dbRoute.GetRoute((long)dataReader["FK_RouteId"]),
                        Status = (ReservationStatusEnum)Enum.Parse(typeof(ReservationStatusEnum), (string)dataReader["Status"])
                    };
                if (resv != null)
                {
                    resv.PartReservations = dbPartReservation.GetAllPartReservation(resv.Id);
                }
            }
            else //This means that a dataset will be used
            {
                var dataRow = dataSetOrDataReader as DataRow;

                if (dataRow != null)
                    resv = new Reservation
                    {
                        Id = (long)dataRow["Id"],
                        Customer = customers.FirstOrDefault(c =>
                        c.Id.Equals((long)dataRow["FK_PersonId"])),
                        Date = (DateTime)dataRow["Date"],
                        Route = routes.FirstOrDefault(r => r.Id.Equals((long)dataRow["FK_RouteId"])),
                        Status = (ReservationStatusEnum)Enum.Parse(typeof(ReservationStatusEnum), (string)dataRow["Status"])
                    };
                dbPartReservation = new DBPartReservation();
                if (resv != null)
                {
                    resv.PartReservations = dbPartReservation.GetAllPartReservation(resv.Id);
                }
            }
            //
            object o = resv;
            return o;
        }
    }
}